import { Service } from 'egg';
import * as SMSClient from '@alicloud/sms-sdk';
import { IdentifyingCode } from '../model/Identifying_code';
import { Employee } from '../model/Employee';
import * as md5 from 'MD5';
import { WorkUnit } from '../model/Work_unit';
import { Project } from '../model/Project';
import { GradeRule } from '../model/Grade_rule';
import { GradeTarget } from '../model/Grade_target';

export default class EmployeeService extends Service {
    /**
     * 新增职员
     * @param param 
     */
    public async createEmployee(param: any) {
        try {
            const checkResult: any = await this.ctx.model.Employee.findOne<Employee>({ attributes: ['employee_id'], where: { employee_account: param.employee_account, is_delete: '0' } });
            if (checkResult) {
                throw new Error(JSON.stringify({ code: 2, msg: '该账号已被注册' }));
            }
            const submitData: any = {};
            //生成主键id
            submitData.employee_id = this.ctx.helper.genUUID();
            submitData.employee_name = param.employee_name;
            submitData.employee_account = param.employee_account;
            submitData.employee_password = md5(param.employee_password + 'linglan');
            submitData.work_unit_id = param.work_unit_id;
            submitData.department_id = param.department_id;
            submitData.position_id = param.position_id;
            submitData.employee_status = '2';
            submitData.created_by = param.admin_id;
            submitData.is_delete = '0';
            const result: any = await this.ctx.model.Employee.create(submitData);
            if (!result) {
                throw new Error(JSON.stringify({ code: 2, msg: '新增失败' }));
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 删除职员
     * @param param 
     */
    public async deleteEmployee(param: any) {
        try {
            const employee_id = param.employee_id;
            const admin_id = param.admin_id;
            const deleteResult = await this.ctx.model.Employee.update({ updated_by: admin_id, is_delete: '1' }, { where: { employee_id } });
            if (deleteResult[0] <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '删除失败' }));
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 查找职员
     * @param param 
     */
    public async searchEmployee(param: any) {
        try {
            let page = Number(param.page);
            let size = Number(param.size);
            let limit = (page - 1) * size;
            let queryStr = `select e.employee_id,e.employee_name,e.employee_account,w.work_unit_name,d.department_name,p.position_name,e.created_at,e.employee_status 
        from t_employee e inner join t_work_unit w on e.work_unit_id=w.work_unit_id and w.is_delete='0' inner join t_department d on 
        e.department_id=d.department_id and d.is_delete='0' inner join t_position p on e.position_id=p.position_id and p.is_delete='0' where e.is_delete='0'`;
            const actualParam: any = {};
            if (param.work_unit_id) {
                queryStr += ` and e.work_unit_id=:work_unit_id`;
                actualParam.work_unit_id = param.work_unit_id;
            }
            if (param.department_id) {
                queryStr += ` and e.department_id=:department_id`;
                actualParam.department_id = param.department_id;
            }
            if (param.position_id) {
                queryStr += ` and e.position_id=:position_id`;
                actualParam.position_id = param.position_id;
            }
            if (param.register_start_time && param.register_end_time) {
                queryStr += ` and e.created_at between :register_start_time and :register_end_time`;
                actualParam.register_start_time = param.register_start_time + ` 00:00:00`;
                actualParam.register_end_time = param.register_end_time + ` 23:59:59`;
            }
            if (param.employee_status) {
                queryStr += ` and e.employee_status=:status`;
                actualParam.status = param.employee_status;
            }
            if (param.employee_name) {
                queryStr += ` and (e.employee_name like :employee_name or e.employee_account like :employee_name)`;
                actualParam.employee_name = '%' + param.employee_name + '%';
            }
            const queryCount = queryStr + ` order by e.created_at desc`;
            queryStr += ` order by e.created_at desc limit ${limit},${size} `;
            const result: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: actualParam
            })
            const count: any = await this.ctx.model.query(queryCount, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: actualParam
            })
            if (result.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            const returnData = { count: count.length, employee_list: result };
            return returnData;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 查找全部职员
     * 
     */
    public async searchAllEmployee() {
        try {
            let queryStr = `
                SELECT
                    e.employee_id,
                    e.employee_name,
                    e.employee_account,
                    w.work_unit_id,
                    d.department_id,
                    p.position_id,
                    e.created_at,
                    e.employee_status 
                FROM
                    t_employee e
                    INNER JOIN t_work_unit w ON e.work_unit_id = w.work_unit_id 
                    AND w.is_delete = '0'
                    INNER JOIN t_department d ON e.department_id = d.department_id 
                    AND d.is_delete = '0'
                    INNER JOIN t_position p ON e.position_id = p.position_id 
                    AND p.is_delete = '0' 
                WHERE
                    e.employee_status='2' 
                    AND e.is_delete = '0' 
                ORDER BY
                    e.created_at DESC`;
            const result: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT
            })
            if (result.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            return result;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 根据职员id查找职员信息
     * @param param 
     */
    public async searchEmployeeById(param: any) {
        try {
            const employee_id = param.employee_id;
            const result: any = await this.ctx.model.Employee.findOne<Employee>({
                attributes: ['employee_id', 'employee_name', 'employee_account', 'employee_password', 'work_unit_id', 'department_id', 'position_id'],
                where: { employee_id: employee_id, is_delete: '0' }
            });
            if (!result) {
                throw new Error(JSON.stringify({ code: 2, msg: '找不到这个职员' }));
            }
            return result;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 修改职员
     * @param param 
     */
    public async updateEmployee(param: any) {
        try {
            const admin_id = param.admin_id;
            const employee_id = param.employee_id;
            const employee_name = param.employee_name;
            // const employee_password = md5(param.employee_password+'linglan');
            const work_unit_id = param.work_unit_id;
            const department_id = param.department_id;
            const position_id = param.position_id;
            const updateResult: any = await this.ctx.model.Employee.update(
                { employee_name, work_unit_id, department_id, position_id, updated_by: admin_id },
                { where: { employee_id } }
            );
            if (updateResult[0] <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '修改失败' }));
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 更新职员状态
     * @param param 
     */
    public async updateEmployeeStatus(param: any) {
        try {
            const employee_id = param.employee_id;
            const admin_id = param.admin_id;
            const type = param.type;//操作类型（1：通过，2：禁止，3：停用，4：启用）
            let employee_status = '';
            if (type == 1 || type == 4) {
                employee_status = '2';
            } else if (type == 2) {
                employee_status = '3';

            } else if (type == 3) {
                const checkIsEdit: any = await this.checkIsEdit(employee_id);
                if (!checkIsEdit) {
                    throw new Error(JSON.stringify({ code: 2, msg: '该职员已参与考核，不能停用' }))
                }
                employee_status = '4';
            } else {
                throw new Error(JSON.stringify({ code: 4, msg: '参数不全' }));
            }
            const updateResult = await this.ctx.model.Employee.update({ updated_by: admin_id, employee_status }, { where: { employee_id } });
            if (updateResult[0] <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '更新失败' }));
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 发送短信
     */
    public async sendSMS(param: any) {
        try {
            const _this = this;
            return new Promise((resolve) => {
                const code = _this.ctx.helper.genCode();
                const accessKeyId = this.app.config.SMS.AccessKeyID;
                const secretAccessKey = this.app.config.SMS.AccessKeySecret;
                //初始化sms_client
                const smsClient = new SMSClient({ accessKeyId, secretAccessKey });
                smsClient.sendSMS({
                    PhoneNumbers: param.phone,
                    SignName: '画像',
                    TemplateCode: 'SMS_145592097',
                    TemplateParam: `{"code":"${code}"}`
                }).then(async function (res) {
                    const { Code } = res;
                    if (Code == "OK") {
                        const submitData: any = {};
                        submitData.identifying_code_id = _this.ctx.helper.genUUID();
                        submitData.identifying_code = code;
                        submitData.phone = param.phone;
                        await _this.ctx.model.IdentifyingCode.create(submitData);
                        resolve({ code: 1, msg: '发送验证码成功' });
                    }
                }, function (err) {
                    resolve({ code: 2, msg: err });
                });
            })
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 注册
     * @param param 
     */
    public async register(param: any) {
        try {
            const registerStatus: any = await this.ctx.service.settingService.searchSetting({ setting_key: 'registerStatus' });
            const setting_value = registerStatus.setting_value;
            if (setting_value == 2) {
                throw new Error(JSON.stringify({ code: 2, msg: '系统已关闭注册功能' }));
            }
            const resultSMS: any = await this.ctx.model.IdentifyingCode.findOne<IdentifyingCode>({
                attributes: ['identifying_code_id', 'created_at'],
                where: { identifying_code: param.code, phone: param.employee_phone }
            });
            if (!resultSMS) {
                throw new Error(JSON.stringify({ code: 2, msg: '验证码不正确' }));
            }
            const { created_at } = resultSMS;
            const date = new Date();
            const currentDate = date.toLocaleString();
            const minutes = this.ctx.helper.getMinutes(created_at, currentDate);
            if (minutes > 5) {
                throw new Error(JSON.stringify({ code: 2, msg: '验证码已过期' }));
            }
            const employeeResult = await this.ctx.model.Employee.findOne<Employee>({ attributes: ['employee_id'], where: { employee_account: param.employee_phone, is_delete: '0' } });
            if (employeeResult) {
                throw new Error(JSON.stringify({ code: 2, msg: '该账号已被注册' }));
            }
            const tran = await this.ctx.model.transaction();
            try {
                const submitData: any = {};
                submitData.employee_id = this.ctx.helper.genUUID();
                submitData.employee_openId = param.employee_openId;
                submitData.employee_nickname = param.employee_nickname;
                submitData.employee_avaTarUrl = param.employee_avaTarUrl;
                submitData.employee_account = param.employee_phone;
                submitData.employee_password = md5(param.employee_password + 'linglan');
                submitData.employee_status = '1';
                submitData.created_by = submitData.employee_id;
                submitData.is_delete = '0';
                const result: any = await this.ctx.model.Employee.create(submitData);
                if (!result) {
                    throw new Error(JSON.stringify({ code: 2, msg: '注册失败' }));
                }
                const token = this.ctx.helper.genUUID();
                const updateResult: any = await this.ctx.model.Employee.update({ employee_token: token }, { where: { employee_id: submitData.employee_id } });
                if (updateResult[0] <= 0) {
                    throw new Error(JSON.stringify({ code: 2, msg: '注册失败' }));
                }
                return { token };
            } catch (e) {
                await tran.rollback();
                this.ctx.logger.error(e);
                throw new Error(JSON.stringify({ code: 2, msg: '注册失败' }));
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 小程序登录
     * @param param 
     */
    public async login(param: any) {
        try {
            const employeeResult: any = await this.ctx.model.Employee.findOne<Employee>({
                attributes: ['employee_id', 'employee_status'],
                where: { employee_account: param.employee_phone, employee_password: md5(param.employee_password + 'linglan'), is_delete: '0' }
            });
            if (!employeeResult) {
                throw new Error(JSON.stringify({ code: 2, msg: '登录失败，账号或密码错误' }));
            }
            const employee_status = employeeResult.employee_status;
            // if (employee_status == 3) {
            //     throw new Error(JSON.stringify({ code: 2, msg: '您的账号没有通过审核' }));
            // }
            if (employee_status == 4) {
                throw new Error(JSON.stringify({ code: 2, msg: '您的账号已被停用' }));
            }
            const employee_id = employeeResult.employee_id;
            const token = this.ctx.helper.genUUID();//生成token
            const employeeUpdate: any = await this.ctx.model.Employee.update({ employee_token: token }, { where: { employee_id } });
            if (employeeUpdate[0] <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '登录失败，更新token失败' }));
            }
            return { token };
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 更新密码
     * @param param 
     */
    public async updatePassword(param: any) {
        try {
            const resultSMS: any = await this.ctx.model.IdentifyingCode.findOne<IdentifyingCode>({
                attributes: ['identifying_code_id', 'created_at'],
                where: { identifying_code: param.code, phone: param.employee_phone }
            });
            if (!resultSMS) {
                throw new Error(JSON.stringify({ code: 2, msg: '验证码不正确' }));
            }
            const { created_at } = resultSMS;
            const date = new Date();
            const currentDate = date.toLocaleString();
            const minutes = this.ctx.helper.getMinutes(created_at, currentDate);
            if (minutes > 5) {
                throw new Error(JSON.stringify({ code: 2, msg: '验证码已过期' }));
            }
            const employeeResult: any = await this.ctx.model.Employee.findOne<Employee>({ attributes: ['employee_id'], where: { employee_account: param.employee_phone, is_delete: '0' } });
            if (!employeeResult) {
                throw new Error(JSON.stringify({ code: 2, msg: '该号码没有注册，不能修改密码' }));
            }
            const updateEmployee: any = await this.ctx.model.Employee.update({ employee_password: md5(param.employee_password + 'linglan') }, { where: { employee_id: employeeResult.employee_id } });
            if (updateEmployee[0] <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '修改失败' }));
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 根据职员id获取职员的身份、认证状态
     * @param param 
     */
    public async searchEmployeeStatusById(param: any) {
        try {
            const { employee_id } = param;
            const queryStr = `select e.employee_name,e.employee_head_portrait,e.work_unit_id,e.department_id,e.position_id,p.position_is_evaluate,e.employee_status 
            from t_employee e left join t_position p on e.position_id=p.position_id where e.employee_id=:employee_id`;
            const result: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { employee_id }
            })
            return result;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 实名认证
     * @param param 
     */
    public async valiateEmployee(param: any) {
        try {
            const employee_id = param.employee_id;
            const employee_name = param.employee_name;
            const employee_head_portrait = param.employee_head_portrait;
            const work_unit_id = param.work_unit_id;
            const department_id = param.department_id;
            const position_id = param.position_id;
            const result: any = await this.ctx.model.Employee.update(
                { employee_name, employee_head_portrait, work_unit_id, department_id, position_id },
                { where: { employee_id, employee_status: '1' } }
            );
            if (result[0] <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '提交认证失败' }));
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 查找我的考核成绩
     * @param param 
     */
    public async searchMyScore(param: any) {
        try {
            const employee_id = param.employee_id;
            const queryStr = `select p.project_id,p.project_name,p.project_start_time,p.project_end_time,ifnull(temp2.score,0) as score
            from (select project_id,employee_id from t_grade_target where employee_id=:employee_id and is_delete='0' group by project_id) temp1 left join 
            (select project_id,employee_id,sum(grade_result_score) as score from t_grade_result where target_employee_id=:employee_id and is_delete='0' group by project_id) temp2 
            on temp1.project_id=temp2.project_id inner join t_project p on temp1.project_id=p.project_id and p.is_delete='0'`;
            const result: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { employee_id }
            })
            if (result.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            return result;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 查找单位
     * @param param 
     */
    public async searchWorkUnit() {
        try {
            const result: any = await this.ctx.model.WorkUnit.findAll<WorkUnit>({
                attributes: ['work_unit_id', 'work_unit_name'],
                where: { is_delete: '0' },
                order: [['created_at', 'DESC']]
            });
            if (result.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            return result;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 根据单位id查找部门
     * @param param 
     */
    public async searchDepartmentByWorkUnit(param: any) {
        try {
            const work_unit_id = param.work_unit_id;
            const queryStr = `
            select d.department_id,d.department_name from t_unit_department ud inner join t_department d 
            on ud.work_unit_id=:work_unit_id and ud.department_id=d.department_id and d.is_delete='0' where ud.is_delete='0' order by ud.created_at desc`;
            const departmentResult: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { work_unit_id }
            })
            if (departmentResult.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            return departmentResult;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 根据部门id查找职位
     * @param param 
     */
    public async searchPositionByDepartment(param: any) {
        try {
            const department_id = param.department_id;
            const queryStr = `
            select p.position_id,p.position_name from t_department_position d inner join t_position p 
            on d.department_id=:department_id and d.position_id=p.position_id and p.is_delete='0' where d.is_delete='0' order by p.created_at desc`;
            const positionResult: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { department_id }
            })
            if (positionResult.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            return positionResult;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 查找考核项目与规则
     * @param param 
     */
    public async searchGradeAndRule(param: any) {
        try {
            const queryStr = `select p.project_id,p.project_name,p.project_min,p.project_max,p.project_is_self from t_grade_employee g inner join t_project p on g.employee_id=:employee_id and g.is_delete='0' and 
            g.project_id=p.project_id and p.project_status='2' and p.is_delete='0'`;
            const project_list: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { employee_id: param.employee_id }
            })
            if (project_list.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '您还没有可以考核的项目' }));
            }
            const project_arr: any = [];
            for (let i = 0; i < project_list.length; i++) {
                const item: any = {};
                item.project_id = project_list[i].project_id;
                item.project_name = project_list[i].project_name;
                item.project_min = project_list[i].project_min;
                item.project_max = project_list[i].project_max;
                item.project_is_self = project_list[i].project_is_self;
                const query_str = `select position_id,project_score from t_grade_rule where project_id=:project_id and is_delete='0'`;
                const result: any = await this.ctx.model.query(query_str, {
                    type: this.ctx.model.QueryTypes.SELECT,
                    replacements: { project_id: item.project_id }
                })
                item.grade_rule = result;
                project_arr.push(item);
            }
            return project_arr;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 检查是否有考核项目
     * @param param 
     */
    public async checkHasProject(param: any) {
        try {
            await this.ctx.service.projectService.startProject();
            await this.ctx.service.projectService.endProject();
            const queryStr = `select p.project_id,p.project_name,p.project_min,p.project_max,p.project_is_self from t_grade_employee g inner join t_project p on g.employee_id=:employee_id and g.is_delete='0' and 
        g.project_id=p.project_id and p.project_status='2' and p.is_delete='0'`;
            const project_list: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { employee_id: param.employee_id }
            })
            return { count: project_list.length };
        } catch (e) {
            throw new Error(e.message);
        }
    }

    /**
     * 查找被考核职员列表
     * @param param 
     */
    public async searchGradeEmployeeList(param: any) {
        try {
            const page = Number(param.page);
            const size = Number(param.size);
            const limit = (page - 1) * size;
            const submitData: any = {};
            submitData.employee_id = param.employee_id;
            let queryStr = `
                    SELECT
                        * 
                    FROM
                        (
                    SELECT
                        p.project_id,
                        gt.employee_id,
                        e.employee_name,
                        e.work_unit_id,
                        e.department_id,
                        e.employee_head_portrait,
                        e.created_at,
                        ifnull( gr.grade_result_score, 0 ) AS score 
                    FROM
                        t_grade_employee g
                        INNER JOIN t_project p ON g.employee_id =:employee_id
                        AND g.is_delete = '0' 
                        AND g.project_id = p.project_id 
                        AND p.project_status = '2' 
                        AND p.is_delete = '0'
                        INNER JOIN t_grade_target gt ON p.project_id = gt.project_id 
                        AND gt.is_delete = '0'
                        INNER JOIN t_employee e 
                        ON e.employee_id = gt.employee_id 
                        AND e.employee_status = '2' 
                        AND e.is_delete = '0'
                        LEFT JOIN t_grade_result gr ON p.project_id = gr.project_id 
                        AND gt.employee_id = gr.target_employee_id 
                        AND gr.employee_id=:employee_id 
                        AND gr.is_delete = '0' 
                        ) temp 
                    WHERE
                        '1' = '1'`;
            if (param.work_unit_id) {
                queryStr += ` AND temp.work_unit_id=:work_unit_id`;
                submitData.work_unit_id = param.work_unit_id;
            } //else {
            //     queryStr += ` AND temp.work_unit_id=:work_unit_id`;
            //     submitData.work_unit_id = '4ad471d248eb144f56485434b3329dc8';
            // }
            if (param.department_id) {
                queryStr += ` AND temp.department_id=:department_id`;
                submitData.department_id = param.department_id;
            }
            if (param.employee_name) {
                queryStr += ` AND temp.employee_name like :employee_name`;
                submitData.employee_name = '%' + param.employee_name + '%';
            }
            if (param.beGradeed == 1) {
                queryStr += ` AND temp.score > 0`;
            }
            const queryCount = queryStr + ` ORDER BY temp.created_at DESC`;
            queryStr += ` ORDER BY temp.created_at DESC limit ${limit},${size}`;
            const result: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: submitData
            })
            const count: any = await this.ctx.model.query(queryCount, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: submitData
            })
            if (result.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            const employee_list: any = [];
            for (let i = 0; i < result.length; i++) {
                const item: any = {};
                item.project_id = result[i].project_id;
                item.employee_id = result[i].employee_id;
                item.employee_name = result[i].employee_name;;
                item.employee_head_portrait = result[i].employee_head_portrait;
                item.score = result[i].score;
                const query_str = `select w.work_unit_name,d.department_name,p.position_id,p.position_name from t_employee e inner join t_work_unit w on e.work_unit_id=w.work_unit_id 
                inner join t_department d on e.department_id=d.department_id inner join t_position p on e.position_id=p.position_id where e.employee_id=:employee_id`;
                const resultDepartment: any = await this.ctx.model.query(query_str, {
                    type: this.ctx.model.QueryTypes.SELECT,
                    replacements: { employee_id: item.employee_id }
                })
                item.work_unit_name = resultDepartment[0].work_unit_name;
                item.department_name = resultDepartment[0].department_name;
                item.position_id = resultDepartment[0].position_id;
                item.position_name = resultDepartment[0].position_name;
                employee_list.push(item);
            }
            let queryAllScore = `
                    SELECT
                        p.project_id,
                        gt.employee_id,
                        IFNULL(SUM(gr.grade_result_score),0) as allScore  
                    FROM
                        t_grade_employee g
                        INNER JOIN t_project p ON g.employee_id =:employee_id 
                        AND g.is_delete = '0' 
                        AND g.project_id = p.project_id 
                        AND p.project_status = '2' 
                        AND p.is_delete = '0' 
                        INNER JOIN t_grade_target gt ON p.project_id = gt.project_id 
                        AND gt.is_delete = '0'
                        INNER JOIN t_employee e ON e.employee_id = gt.employee_id 
                        AND e.is_delete = '0'
                        LEFT JOIN t_grade_result gr ON p.project_id = gr.project_id 
                        AND gt.employee_id = gr.target_employee_id 
                        AND gr.employee_id=:employee_id 
                        AND gr.is_delete = '0' 
                    GROUP BY
                        p.project_id`;
            const resultAllCount: any = await this.ctx.model.query(queryAllScore, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { employee_id: param.employee_id }
            });
            const returnData: any = { alreadyGraded: resultAllCount, count: count.length, employee_list: employee_list };
            return returnData;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 根据token换取employee_id
     * @param token 
     */
    public async searchEmployeeByToken(token) {
        try {
            const result: any = await this.ctx.model.Employee.findOne<Employee>({ attributes: ['employee_id'], where: { employee_token: token } });
            if (!result) {
                return '';
            }
            return result.employee_id;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 根据token判断是否登录
     * @param token 
     */
    public async checkEmployeeByToken(token) {
        try {
            const result: any = await this.ctx.model.Employee.findOne<Employee>({ attributes: ['employee_id'], where: { employee_token: token } });
            if (!result) {
                throw new Error(JSON.stringify({ code: -1, msg: '请登录' }));
            }
            return true
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 给职员打分
     * @param param 
     */
    public async markingEmployee(param: any) {
        try {
            let { project_id, employee_id, target_employee_id, grade_result_score, position_id } = param;
            grade_result_score = Number(grade_result_score);
            const project: any = await this.ctx.model.Project.findOne<Project>({ attributes: ['project_min', 'project_max', 'project_is_self'], where: { project_id } });
            if (!project) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有找到该考核项目' }));
            }
            const { project_min, project_max, project_is_self } = project;
            if (project_is_self == 2) {
                if (employee_id === target_employee_id) {
                    throw new Error(JSON.stringify({ code: 2, msg: '该项目规定禁止给自己打分' }));
                }
            }
            if (grade_result_score !== project_min) {
                throw new Error(JSON.stringify({ code: 2, msg: '最小打分单位不符合该项目的规定' }));
            }
            const hasScoreResult: any = await this.ctx.model.GradeResult.findOne({
                attributes: ['grade_result_id', 'grade_result_score'],
                where: { project_id, employee_id, target_employee_id, is_delete: '0' }
            });
            if (hasScoreResult) {
                const result_score = hasScoreResult.grade_result_score;
                if (result_score + grade_result_score > project_max) {
                    throw new Error(JSON.stringify({ code: 2, msg: '您已超过该项目的最大打分数' }));
                }
            }
            const gradeRule: any = await this.ctx.model.GradeRule.findOne<GradeRule>({ attributes: ['project_score'], where: { project_id, position_id, is_delete: '0' } });
            if (!gradeRule) {
                throw new Error(JSON.stringify({ code: 2, msg: '该职位没有设置可加分数' }));
            }
            const project_score = gradeRule.project_score;
            if (project_score <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '该职位可加分数小于等于0' }));
            }
            if (grade_result_score > project_score) {
                throw new Error(JSON.stringify({ code: 2, msg: '分数已超过职位的可加分数' }));
            }
            const queryGradeResult = `select ifnull(sum(g.grade_result_score),0) as score from t_grade_result g inner join t_employee e on g.target_employee_id=e.employee_id and e.is_delete='0' 
            inner join t_grade_target gt on g.target_employee_id=gt.employee_id and gt.is_delete='0' and g.project_id=gt.project_id where g.project_id=:project_id and g.employee_id=:employee_id and g.is_delete='0'`;
            const gradeResultScore: any = await this.ctx.model.query(queryGradeResult, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { project_id, employee_id }
            })
            if (gradeResultScore.length > 0) {
                const hasGradeResultScore = Number(gradeResultScore[0].score);
                if (hasGradeResultScore + grade_result_score > project_score) {
                    throw new Error(JSON.stringify({ code: 2, msg: '分数已超过职位的可加分数' }));
                }
                if (hasScoreResult) {
                    const grade_result_id = hasScoreResult.grade_result_id;
                    let result_score = hasScoreResult.grade_result_score + grade_result_score;
                    const result: any = await this.ctx.model.GradeResult.update({ grade_result_score: result_score }, { where: { grade_result_id } });
                    if (result[0] <= 0) {
                        throw new Error(JSON.stringify({ code: 2, msg: '打分失败' }));
                    }
                } else {
                    const grade_result_id = this.ctx.helper.genUUID();
                    const submitData: any = {};
                    submitData.grade_result_id = grade_result_id;
                    submitData.project_id = project_id;
                    submitData.employee_id = employee_id;
                    submitData.target_employee_id = target_employee_id;
                    submitData.grade_result_score = grade_result_score;
                    submitData.is_delete = '0';
                    const result: any = await this.ctx.model.GradeResult.create(submitData);
                    if (!result) {
                        throw new Error(JSON.stringify({ code: 2, msg: '打分失败' }));
                    }
                }
            } else {
                const grade_result_id = this.ctx.helper.genUUID();
                const submitData: any = {};
                submitData.grade_result_id = grade_result_id;
                submitData.project_id = project_id;
                submitData.employee_id = employee_id;
                submitData.target_employee_id = target_employee_id;
                submitData.grade_result_score = grade_result_score;
                submitData.is_delete = '0';
                const result: any = await this.ctx.model.GradeResult.create(submitData);
                if (!result) {
                    throw new Error(JSON.stringify({ code: 2, msg: '打分失败' }));
                }
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 判断职员是否可停用
     * @param employee_id 
     */
    public async checkIsEdit(employee_id: string) {
        try {
            const result: any = await this.ctx.model.GradeTarget.findAll<GradeTarget>({ attributes: ['grade_target_id'], where: { employee_id, is_delete: '0' } });
            if (result.length > 0) {
                return false;
            } else {
                return true;
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(JSON.stringify({ code: 2, msg: e.message }));
        }
    }

}